package db.mysql;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Hashtable;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;


public class mySqlUtils {
	
	static String mysqlDriver="org.gjt.mm.mysql.Driver";
	static String mysqlURL="jdbc:mysql://localhost:3306/goomi?useUnicode=true&characterEncoding=utf8";
	//String mysqlURL="jdbc:mysql://localhost:3306/goomi?useUnicode=true&characterEncoding=utf8";
	static String mysqlUser="root";
	static String mysqlPassword="smiles";
	
	public static void main(String args[]) 
	{
		//test
		try{
			mySqlUtils.exec("insert into color_info (id,color) values (3,'blue3')");
		}catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}

	public static ArrayList Query(String cmd) 
	{

		System.out.println("====");
		try {
			cmd = new String(cmd.getBytes("8859_1"),"UTF-8");
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		System.out.println(cmd);
		System.out.println("====");
		
		try
		{
			Class.forName(mysqlDriver);
		}
		catch(Exception e)
		{
			System.out.println("driver:"+mysqlDriver);
			e.printStackTrace();
		}

		try
		{
			Connection mysqlCon = DriverManager.getConnection(mysqlURL,mysqlUser,mysqlPassword);

			Statement st;
			ResultSet rs;
			boolean  qu=false;

			st = mysqlCon.createStatement();
			rs = st.executeQuery(cmd);
			st = mysqlCon.createStatement();                            
			ArrayList ret = new ArrayList();
			ResultSetMetaData rsmd = rs.getMetaData();
			String columnName="",rsValue="";
			System.out.println("rs="+rs.getRow());
			while ( rs.next() ) {
				HashMap tab = new HashMap();
    			for(int i=1 ; (i<rsmd.getColumnCount()+1) ; i++){
                    columnName=(String)rsmd.getColumnLabel(i);
    				if((rs.getString(columnName) == null)||(((rs.getString(columnName)).trim()).equals("null")))
    					rsValue="";
    				else{
    					rsValue = rs.getString(columnName);
    				}
    		    		tab.put(columnName.toLowerCase(),rsValue.trim());
    			}
    			tab.put("message", "OK");
    			if(tab.get("id") != null)tab.put("db_id",tab.get("id"));
    		    ret.add(tab);
    			tab= null;
    	    }

			st.close();

			mysqlCon.close();
			return ret;
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
		}
		return null;
	}

	public static void commonDbAction(HttpServletRequest request) throws SQLException, UnsupportedEncodingException 
	{
//		request.setCharacterEncoding("uft8");
		String oper = request.getParameter("oper");
		String table = request.getParameter("table");
		HashSet jump = new HashSet();
		jump.add("oper");
		jump.add("table");
		jump.add("action");
		jump.add("db_id");
		
		System.out.println("oper:" + oper);
		if("add".equals(oper)){
			String sql = "insert into " + table + " ( ";
			String value = "";
			Enumeration columnNames=request.getParameterNames();  
			while(columnNames.hasMoreElements()){  
				String name=(String)columnNames.nextElement();
				if(jump.contains(name))continue;
				String val = request.getParameter(name);
				sql +=  name + " ,";
				if("id".equals(name) && "_empty".equals(val)){
					ArrayList id = Query("select max(CAST(id AS UNSIGNED)) + 1 as id from " + table );
					val = (String)((Map)id.get(0)).get("id");
					if(val == null || "".equals(val))val = "1";
					value += "," + val ;
				}else{
					val = org.apache.commons.lang.StringEscapeUtils.escapeSql(val);
					value += ",'" + val + "'";
				}
			} 
			sql = sql.substring(0, sql.length() -1);
			sql = sql + ") values (" + value.substring(1) + ")";
			System.out.println("sql:" + sql);
			exec(sql);
		}else if("edit".equals(oper)){
			String sql = "update " + table + " set ";
			String value = "";
			Enumeration columnNames=request.getParameterNames();  
			while(columnNames.hasMoreElements()){ 
				String name=(String)columnNames.nextElement();
				if(jump.contains(name) || "id".equals(name))continue;
				String val = request.getParameter(name);
				val = org.apache.commons.lang.StringEscapeUtils.escapeSql(val);
				value += "," + name + " = '" + val + "'";
			} 
			String id = request.getParameter("id");
			if(request.getParameter("db_id") != null) id = request.getParameter("db_id");
			sql = sql + value.substring(1) + " where id = '" + id + "'";
			System.out.println("sql:" + sql);
			exec(sql);
		}else if("delete".equals(oper)){
			String id = request.getParameter("id");
			if(request.getParameter("db_id") != null) id = request.getParameter("db_id");
			String sql = "delete FROM " + table + " where id = '" + id + "'";
			System.out.println("sql:" + sql);
			exec(sql);
		}
	}
	
	
	public static void exec(String cmd) throws SQLException, UnsupportedEncodingException
	{

		System.out.println("====1");
		cmd = cmd.replace("''", "null");
		cmd = new String(cmd.getBytes("8859_1"),"UTF-8");
		System.out.println(cmd);
		System.out.println("====");
		
		try
		{
			//Class.forName(mysqlDriver);
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		}
		catch(Exception e)
		{
			System.out.println("driver:"+mysqlDriver);
			e.printStackTrace();
		}

		try
		{
			Connection mysqlCon = DriverManager.getConnection(mysqlURL,mysqlUser,mysqlPassword);

			Statement st;
			ResultSet rs;
			boolean  qu=false;

			st = mysqlCon.createStatement();
			st.execute(cmd);
			st.close();

			mysqlCon.close();
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
			throw ex;
		}
		System.out.println("exec over");
	}

	public static void execNotCommit(String cmd) throws Exception 
	{

		System.out.println("====");
		cmd = new String(cmd.getBytes("8859_1"),"UTF-8");
		System.out.println(cmd);
		System.out.println("====");
		
		try
		{
			Class.forName(mysqlDriver);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new Exception(e.getMessage());
		}

		try
		{
			Connection mysqlCon = DriverManager.getConnection(mysqlURL,mysqlUser,mysqlPassword);
			
			mysqlCon.setAutoCommit(false);
			Statement st;
			ResultSet rs;
			boolean  qu=false;

			//execute�O�_�|�Ǧ^���G
			st = mysqlCon.createStatement();
			st.execute(cmd);                //�إ�Statement����
			
			st.close();
			
			mysqlCon.rollback();
			mysqlCon.close();
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
			throw new Exception(ex.getMessage());
		}
	}

	public static void execPath(ArrayList sqlArray) throws Exception 
	{


		
		try
		{
			Class.forName(mysqlDriver);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new Exception(e.getMessage());
		}

		try
		{
			Connection mysqlCon = DriverManager.getConnection(mysqlURL,mysqlUser,mysqlPassword);
			
			mysqlCon.setAutoCommit(false);
			try{
				Statement st;
				ResultSet rs;
				boolean  qu=false;

				for(int i = 0 ; i < sqlArray.size() ; i++){
					String cmd = (String)sqlArray.get(i);
					System.out.println("====");
					cmd = new String(cmd.getBytes("8859_1"),"UTF-8");
					System.out.println(cmd);
					System.out.println("====");
					st = mysqlCon.createStatement();
					st.execute(cmd); 
					st.close();
				}
				
				mysqlCon.commit();
			}catch(Exception e){
				mysqlCon.rollback();
				throw new Exception(e.getMessage());
			}
			
			mysqlCon.close();
		}
		catch(SQLException ex)
		{
			ex.printStackTrace();
			throw new Exception(ex.getMessage());
		}
	}
	
	public static Connection getConn() throws SQLException{
		try
		{
			//Class.forName(mysqlDriver);
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return DriverManager.getConnection(mysqlURL,mysqlUser,mysqlPassword);
	}
	
	
}
